/**
 * @Author: Zerone
 * @Date: 2016.12.26
 * <p>
 * There are a total of twelve major categories of videos in www.bilibili.com.
 * Whereupon, I created twelve tables for each category of videos, which will
 * help me find out the top 3 favorite videos for each category easily.
 * </p>
 */

package database;

import video.Video;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashSet;

public class DataBase {
    private static Connection conn = null;

    private static void init() throws Exception {
        // Connect to SQLite's JDBC
        Class.forName("org.sqlite.JDBC");

        // Create a connection with the database.
        conn = DriverManager.getConnection("jdbc:sqlite:db/homework_5.db");
        conn.setAutoCommit(false);
    }

    /**
     * Judge which table should these videos insert.
     *
     * @return The tid corresponds to the table number
     */
    private static int calTNum(int tid) {
        int ret = 0;
        switch (tid) {
            // 动画
            case 24:
            case 25:
            case 27:
            case 47:
                ret = 1;
                break;
            // 番剧
            case 33:
            case 32:
            case 153:
            case 152:
            case 51:
                ret = 2;
                break;
            //音乐
            case 30:
            case 31:
            case 28:
            case 59:
            case 29:
            case 54:
            case 130:
                ret = 3;
                break;
            // 舞蹈
            case 20:
            case 154:
            case 156:
                ret = 4;
                break;
            // 游戏
            case 17:
            case 65:
            case 136:
            case 19:
            case 121:
                ret = 5;
                break;
            //科技
            case 37:
            case 124:
            case 122:
            case 39:
            case 96:
            case 95:
            case 98:
                ret = 6;
                break;
            // 生活
            case 138:
            case 21:
            case 76:
            case 161:
            case 75:
            case 162:
            case 163:
                ret = 7;
                break;
            //鬼畜
            case 22:
            case 26:
            case 127:
            case 126:
                ret = 8;
                break;
            // 时尚
            case 157:
            case 158:
            case 159:
            case 164:
                ret = 9;
                break;
            // 广告
            case 166:
                ret = 10;
                break;
            // 娱乐
            case 71:
            case 137:
            case 131:
                ret = 11;
                break;
            // 影视
            case 82:
            case 85:
            case 145:
            case 146:
            case 147:
            case 83:
                ret = 12;
                break;
        }
        return ret;
    }

    public synchronized static void save(HashSet<Video> v, int tid) throws Exception {
        String sql = "INSERT INTO v" + calTNum(tid) +
                "(aid,author,tname,coin,favorites)" +
                "VALUES (?,?,?,?,?)";
        if (conn == null) {
            init();
        }
        PreparedStatement insert = conn.prepareStatement(sql);

        int count = 0;
        long begin = System.currentTimeMillis();
        for (Video video : v) {
            insert.setInt(1, video.getAid());
            insert.setString(2, video.getAuthor());
            insert.setString(3, video.getTname());
            insert.setInt(4, video.getCoin());
            insert.setInt(5, video.getFavorites());
            count++;
            insert.addBatch();

            // Every 10000 data will insert once
            if (count % 10000 == 0) {
                insert.executeBatch();
                conn.commit();
                insert.clearBatch();
            }
        }

        // Insert less than 10000 of the data.
        insert.executeBatch();
        double end = System.currentTimeMillis();
        // Display the time spent.
        System.out.println(tid + ": " + (end - begin) / 1000 + " sec");
        conn.commit();
        insert.close();
    }

    /**
     * Acquire data from corresponding table.
     * @param category 0 - 12
     * @return
     */
    public static ArrayList<Video> getVideos(int category) throws Exception {
        if (conn == null) {
            init();
        }

        ArrayList<Video> ret = new ArrayList<>();
        Statement stat = conn.createStatement();
        for (int i = 1; i <= 12; ++i) {
            String sql = "select * from v" + category + ";";
            ResultSet rs = stat.executeQuery(sql);

            while (rs.next()) {
                int aid = rs.getInt(1);
                String author = rs.getString(2);
                String tname = rs.getString(3);
                int coin = rs.getInt(4);
                int favorites = rs.getInt(5);
                Video v = new Video(aid, author, tname, coin, favorites);
                ret.add(v);
            }
            rs.close();
        }
        return ret;
    }

    /**
     * Find out the top 3 favorite videos for each category.
     */
    public static HashSet<Video> getTopVideos() throws Exception {
        if (conn == null) {
            init();
        }

        HashSet<Video> ret = new HashSet<>();
        Statement stat = conn.createStatement();
        for (int i = 1; i <= 12; ++i) {
            String sql = "select * from v" + i + " order by favorites desc;";
            ResultSet rs = stat.executeQuery(sql);

            int count = 0;
            while (rs.next() && count < 3) {
                int aid = rs.getInt(1);
                String author = rs.getString(2);
                String tname = rs.getString(3);
                int coin = rs.getInt(4);
                int favorites = rs.getInt(5);
                Video v = new Video(aid, author, tname, coin, favorites);
                ret.add(v);
                count++;
            }
            rs.close();
        }
        return ret;
    }

    /**
     * Get request url of each category.
     */
    public static ArrayList<String> getURLS() throws Exception {
        if (conn == null) {
            init();
        }
        ArrayList<String> ret = new ArrayList<>();
        Statement stat = conn.createStatement();

        // Attain all of videos' attributes.
        ResultSet rs = stat.executeQuery("select * from urls;");

        // Store the results.
        while (rs.next()) {
            ret.add(rs.getString("url"));
        }
        rs.close();
        System.out.println("Target urls have been loaded.");
        return ret;
    }
}